﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using MyLib;

namespace Billing2.cls
{
    public partial class ClsImportSubscriber
    {
        void ImportOrganization(ClsImportSubscriber.Organization org, DBCommands2 db)
        {
            if (org.site == "" && org.costcentre == "")
            {
                //insert org
                db.Organization.insert(org.org, org.org_fullname, 0, 1, 1, DateTime.Now, 1, DateTime.Now);
            }
            else if (org.costcentre == "")
            {
                //insert site
                DataTable dt = db.Organization.select("where OrgIDName = @OrgIDName", org.org);
                if (dt == null || dt.Rows.Count == 0)
                    throw new Exception("Can not find organization '" + org.org + "'");
                if (dt.Rows.Count > 1)
                    throw new Exception("there are " + dt.Rows.Count.ToString() + " organizations have same name '" + org.org + "'");
                long org_id = (long)dt.Rows[0]["ID"];
                db.SitesInOrganization.insert((int)org_id, org.site, org.site, 1, 1, DateTime.Now, 1, DateTime.Now);
            }
            else
            {
                //insert cc
                DataTable dt = db.SitesInOrganization.select("where SiteIDName = @SiteIDName", org.site);
                if (dt == null || dt.Rows.Count == 0)
                    throw new Exception("Can not find site '" + org.site + "'");
                if (dt.Rows.Count > 1)
                    throw new Exception("there are " + dt.Rows.Count.ToString() + " sites have same name '" + org.site + "'");
                long site_id = (long)dt.Rows[0]["ID"];
                db.CostCentresInSite.insert((int)site_id, org.costcentre, org.costcentre, 1, 1, DateTime.Now, 1, DateTime.Now);
            }
        }

        public long CreateChangeRequest(ClsImportSubscriber.Subscriber sub, DBCommands2 db)
        {
            DataTable dt = null;
            long subscriber_id = 0;
            switch (this.ChangeRequestType)
            {
                case ClsChangeRequestHelper.ChangeRequestType.Create:
                    subscriber_id = 0;
                    break;
                case ClsChangeRequestHelper.ChangeRequestType.Teminate:
                case ClsChangeRequestHelper.ChangeRequestType.Update:
                case ClsChangeRequestHelper.ChangeRequestType.CostCentreTransfer:
                    dt = db.Subscriber.select("where AssetID=@AssetID order by EffectiveDate desc", sub.AssetID);
                    subscriber_id = (long)dt.Rows[0]["ID"];
                    break;
                default:
                    throw new Exception("Do not support batch " + this.ChangeRequestType.ToString() + "!");
            }

            string ChangeRequestNo = GetNewCRNo((Int64)ClsChangeRequestHelper.ChangeRequestStatus.Submitted, db);
            db.ChangeRequest.insert(
                ChangeRequestNo,
                Convert.ToInt32(this.ChangeRequestType),
                (int)ClsChangeRequestHelper.ChangeRequestStatus.Submitted,
                "(Batch " + this.ChangeRequestType.ToString() + ")" + sub.Remarks,
                (int)LoginUser.UserID,
                DateTime.Now,
                (int)LoginUser.UserID,
                DateTime.Now,
                0);
            dt = db.Select("select @@IDENTITY");
            long cr_id = Convert.ToInt64(dt.Rows[0][0]);

            dt = db.CostCentresInSite.select("where CostCentreIDName = @CostCentreIDName", sub.Costcentre);
            if (dt.Rows.Count == 0)
                throw new Exception(string.Format("Cannot find Costcentre({0})", sub.Costcentre));
            if (dt.Rows.Count > 1)
                throw new Exception(string.Format("Costcentre name duplicate '{0}'", sub.Costcentre));
            long cc_id = (long)dt.Rows[0]["ID"];

            db.SP_DetailsInChangeRequest_DeleteByChangeRequestID((int)cr_id);
            int detail_id = 0;
            db.SP_DetailsInChangeRequest_Insert2(
                (int)cr_id,
                (int)subscriber_id,
                (int)cc_id,
                sub.Subscription_Start_Date != "" ? sub.Subscription_Start_Date.ToDateTime_dd_MM_yyyy() : DateTime.MinValue,
                sub.Subscription_End_Date != "" ? sub.Subscription_End_Date.ToDateTime_dd_MM_yyyy() : DateTime.MinValue,
                "",
                sub.User_Name,
                sub.Service_Type,
                sub.AssetID,
                "", //sub.Hostname,
                "",
                (this.ChangeRequestType == ClsChangeRequestHelper.ChangeRequestType.Update ? (sub.New_Existing_Terminated == "Active" ? 1 : 0) : 1),
                ref detail_id);

//            switch (this.ChangeRequestType)
//            {
//                case ClsChangeRequestHelper.ChangeRequestType.Create:                    
//                    db.ExecSqlCmd(@"
//				        insert into DetailsInChangeRequest(
//					        ChangeRequestID, New_CostCentreID, New_EffectiveDate, New_TerminatedDate,
//                            New_Email, New_Username, New_AssetID, New_ServiceType, New_Hostname, New_OS, New_Status,
//					        Old_CostCentreID, Old_EffectiveDate, Old_TerminatedDate,
//                            Old_TerminatedBy, Old_CreatedBy, Old_CreatedDatetime, Old_ModifiedBy, Old_ModifiedDateTime,
//					        Old_Email, Old_Username, Old_AssetID, Old_ServiceType, Old_Hostname, Old_OS, Old_Status)
//				        values(
//					        @ChangeRequestID, @New_CostCentreID, @New_EffectiveDate, @New_TerminatedDate,
//					        @New_Email, @New_Username, @New_AssetID, @New_ServiceType, @New_Hostname, @New_OS, @New_Status,
//					        @Old_CostCentreID, @Old_EffectiveDate, @Old_TerminatedDate,
//					        @Old_TerminatedBy, @Old_CreatedBy, @Old_CreatedDatetime, @Old_ModifiedBy, @Old_ModifiedDateTime,
//					        @Old_Email, @Old_Username, @Old_AssetID, @Old_ServiceType, @Old_Hostname, @Old_OS, @Old_Status)
//				        ",
//                        cr_id, cc_id, sub.Subscription_Start_Date.ToDateTime(),
//                        sub.Subscription_End_Date == "" ? (object)DBNull.Value : sub.Subscription_End_Date..ToDateTime(),
//                        "", sub.User_Name, sub.AssetID, sub.Service_Type, sub.Hostname, "", 1,
//                        DBNull.Value, DBNull.Value, DBNull.Value,
//                        DBNull.Value, DBNull.Value, DBNull.Value, DBNull.Value, DBNull.Value,
//                        DBNull.Value, DBNull.Value, DBNull.Value, DBNull.Value, DBNull.Value, DBNull.Value, DBNull.Value);
//                    break;
//                case ClsChangeRequestHelper.ChangeRequestType.Teminate:
//                    db.ExecSqlCmd(@"
//				        insert into DetailsInChangeRequest(
//					        ChangeRequestID,
//                            New_CostCentreID, New_EffectiveDate, New_TerminatedDate,
//                            New_Email, New_Username, New_AssetID, New_ServiceType, New_Hostname, New_OS, New_Status,
//					        Old_CostCentreID, Old_EffectiveDate, Old_TerminatedDate, Old_TerminatedBy,
//                            Old_CreatedBy, Old_CreatedDatetime, Old_ModifiedBy, Old_ModifiedDateTime,
//					        Old_Email, Old_Username, Old_AssetID, Old_ServiceType, Old_Hostname, Old_OS, Old_Status)
//				        select
//                            @ChangeRequestID,
//                            CostCentreID, EffectiveDate, @New_TerminatedDate,
//					        Email, Username, AssetID, ServiceType, Hostname, OS, Status,
//                            CostCentreID, EffectiveDate, TerminatedDate,TerminatedBy,
//                            CreatedBy,CreatedDatetime,ModifiedBy,ModifiedDateTime,
//                            Email, Username, AssetID, ServiceType, Hostname, OS, Status
//		                from Subscriber
//                        WHERE AssetID = @AssetID
//                        ", cr_id, DateTime.Parse(sub.Subscription_End_Date), sub.AssetID);
//                    break;
//                case ClsChangeRequestHelper.ChangeRequestType.Update:
//                    db.ExecSqlCmd(@"
//				        insert into DetailsInChangeRequest(
//					        ChangeRequestID,
//                            New_CostCentreID, New_EffectiveDate, New_TerminatedDate,
//                            New_Email, New_Username, New_AssetID, New_ServiceType, New_Hostname, New_OS, New_Status,
//					        Old_CostCentreID, Old_EffectiveDate, Old_TerminatedDate, Old_TerminatedBy,
//                            Old_CreatedBy, Old_CreatedDatetime, Old_ModifiedBy, Old_ModifiedDateTime,
//					        Old_Email, Old_Username, Old_AssetID, Old_ServiceType, Old_Hostname, Old_OS, Old_Status)
//				        select
//                            @ChangeRequestID,
//                            CostCentreID, EffectiveDate, TerminatedDate,
//					        Email, @Username, AssetID, ServiceType, @Hostname, OS, Status,
//                            CostCentreID, EffectiveDate, TerminatedDate,TerminatedBy,
//                            CreatedBy,CreatedDatetime,ModifiedBy,ModifiedDateTime,
//                            Email, Username, AssetID, ServiceType, Hostname, OS, Status
//		                from Subscriber
//                        WHERE AssetID = @AssetID
//                        ", cr_id, sub.User_Name, sub.Hostname, sub.AssetID);
//                    break;
//                case ClsChangeRequestHelper.ChangeRequestType.CostCentreTransfer:
//                    db.ExecSqlCmd(@"
//				        insert into DetailsInChangeRequest(
//					        ChangeRequestID,
//                            New_CostCentreID, New_EffectiveDate, New_TerminatedDate,
//                            New_Email, New_Username, New_AssetID, New_ServiceType, New_Hostname, New_OS, New_Status,
//					        Old_CostCentreID, Old_EffectiveDate, Old_TerminatedDate, Old_TerminatedBy,
//                            Old_CreatedBy, Old_CreatedDatetime, Old_ModifiedBy, Old_ModifiedDateTime,
//					        Old_Email, Old_Username, Old_AssetID, Old_ServiceType, Old_Hostname, Old_OS, Old_Status)
//				        select
//                            @ChangeRequestID,
//                            @CostCentreID, @EffectiveDate, TerminatedDate,
//					        Email, Username, AssetID, ServiceType, Hostname, OS, Status,
//                            CostCentreID, EffectiveDate, TerminatedDate,TerminatedBy,
//                            CreatedBy,CreatedDatetime,ModifiedBy,ModifiedDateTime,
//                            Email, Username, AssetID, ServiceType, Hostname, OS, Status
//		                from Subscriber
//                        WHERE AssetID = @AssetID
//                        ", cr_id, cc_id,  DateTime.Parse(sub.Subscription_Start_Date), sub.AssetID);
//                    break;
//                default:
//                    throw new Exception("Do not support batch " + this.ChangeRequestType.ToString() + "!");
//            }
            return cr_id;
        }

        public string GetNewCRNo(Int64 xCRStatus, DBCommands2 db)
        {
            int CounterLength = 5;
            string crPrefix = "CR-" + DateTime.Now.ToString("yyyyMMdd-");

            string crNo = "";
            crNo = db.SelectString(@"
                SELECT isnull(MAX(ChangeRequestNo), '')
		        FROM ChangeRequest 
		        WHERE (CONVERT(varchar(8), CreatedDatetime, 112) = CONVERT(varchar(8), GETDATE(), 112))
			        AND ChangeRequestStatus = @ChangeRequestStatus
                ", xCRStatus);
            if (crNo == "")
                return (crPrefix + "00001");
            crNo = (Convert.ToInt32(crNo.Substring(crNo.Length - CounterLength, CounterLength)) + 1).ToString();
            string EmptyNumber = "";
            for (int i = crNo.Length; i < CounterLength; i++)
            {
                EmptyNumber = EmptyNumber + "0";
            }
            return (crPrefix + EmptyNumber + crNo);
        }

//        void ImportSubscriber(ClsImportSubscriber.Subscriber sub, DBCommands db)
//        {
//            DataTable dt = null;
//            db.CostCentresInSite.select(ref dt, "where CostCentreIDName = @CostCentreIDName", sub.Costcentre);
//            if (dt == null || dt.Rows.Count == 0)
//                throw new Exception("Can not find cost centre '" + sub.Costcentre + "'");
//            if (dt.Rows.Count > 1)
//                throw new Exception("there are " + dt.Rows.Count.ToString() + " cost centre have same name '" + sub.Costcentre + "'");
//            long cc_id = (long)dt.Rows[0]["ID"];

//            switch (this.ChangeRequestType)
//            {
//                case ClsChangeRequestHelper.ChangeRequestType.Create:
//                    db.ExecSqlCmd(@"
//				        insert into Subscriber(
//					        ServiceFormID, MasterProfileID, EffectiveDate, TerminatedDate, TerminatedBy,
//					        CreatedBy, CreatedDatetime, ModifiedBy, ModifiedDateTime, CostCentreID,
//					        Email, Username, ServiceType, AssetID, Hostname, OS, Status)
//				        values(
//					        @ServiceFormID, @MasterProfileID, @EffectiveDate, @TerminatedDate, @TerminatedBy,
//					        @CreatedBy, @CreatedDatetime, @ModifiedBy, @ModifiedDateTime, @CostCentreID,
//					        @Email, @Username, @ServiceType, @AssetID, @Hostname, @OS, @Status)
//				        ",
//                        0, 0,
//                        DateTime.Parse(sub.Subscription_Start_Date),
//                        sub.Subscription_End_Date != "" ? DateTime.Parse(sub.Subscription_End_Date) : (object)DBNull.Value,
//                        DBNull.Value,
//                        LoginUser.UserID, DateTime.Now, DBNull.Value, DBNull.Value, (int)cc_id,
//                        "", sub.User_Name, sub.Service_Type, sub.AssetID, sub.Hostname, "", 1);
//                    break;
//                case ClsChangeRequestHelper.ChangeRequestType.Teminate:
//                    db.ExecSqlCmd(@"
//				        update Subscriber
//                        set
//					        TerminatedDate=@TerminatedDate,
//                            TerminatedBy=@TerminatedBy,
//					        Status=@Status
//                        where AssetID=@AssetID
//				        ",
//                        DateTime.Parse(sub.Subscription_End_Date),
//                        LoginUser.UserID,
//                        1,
//                        sub.AssetID);
//                    break;
//                default:
//                    throw new Exception("Do not support batch " + this.ChangeRequestType.ToString() + "!");
//            }
//        }
    }
}